<?php
/**
 * Created by JetBrains PhpStorm.
 * User: zhouyang
 * Date: 13-7-15
 * Time: 下午4:31
 * To change this template use File | Settings | File Templates.
 */
class UsersDB  extends DBReader{

    /**
     * @tudo 根据用户名获取用户信息
     * @param $account
     * @return array|mixed
     */
    public function getUserByAccout($account){

        $sql = 'SELECT user_id, account, password, last_time, last_ip, email, open_email, city, tel, real_name, nick_name, face, levels, weibo, website, tagline , bio, company, title, identity_name, add_time FROM users WHERE lower(account) = lower(:account)';

        $row = $this->db()->getRow($sql, array('account' => $account));

        return $row ? $row : array();

    }

    /**
     * @tudo 根据用户ID获取用户信息
     * @param $user_id
     * @return array|mixed
     */
    public function getUserById($user_id){
        $sql = 'SELECT user_id, account, password, last_time, last_ip, email, open_email, city, tel, real_name, nick_name, face, levels, weibo, website, tagline , bio, company, title, identity_name, add_time FROM users WHERE lower(user_id) = lower(:user_id)';

        $row = $this->db()->getRow($sql, array('user_id' => $user_id));

        return $row ? $row : array();
    }

    public function login($account,$password){
        $sql = 'select user_id, account, password, last_time, last_ip, email, open_email, city, tel, real_name, nick_name, face, levels, weibo, website, tagline , bio, company, title, identity_name, add_time from users where account = :account and password = :password';
        return $this->db()->getRow($sql,array('account'=>$account,'password'=>$password));
    }

    //获取用户所有的权限分类ID
    public function getUserCateIds($user_id){
        static $caches = array();

        if($caches[$user_id]){

            return $caches[$user_id];

        }else{

            $sql = "select cate_id from cate_has_roles cr left join user_has_roles ur on cr.role_id = ur.role_id where ur.user_id = :user_id";
            $rcate_ids = $this->db()->getOneAll($sql,array('user_id'=>$user_id));

            $sql = "select cate_id from cate_has_users where user_id = :user_id";

            $ucate_ids = $this->db()->getOneAll($sql,array('user_id'=>$user_id));

            $cate_ids = array_unique(array_merge($rcate_ids,$ucate_ids));

            $caches[$user_id] = $cate_ids;
            return $cate_ids;
        }
    }

    //是否有权限
    public function isAction($action_url,$user_id = ''){
        $user_id = $user_id ? $user_id : $this->session()->get('user_id');

        $user_cate_ids = $this->getUserCateIds($user_id);

        $sql = "select cate_id from cates where lower(action_url) = :action_url";

        $action_id = $this->db()->getOne($sql,array('action_url'=>$action_url));

        if($action_id){

            if(in_array($action_id,$user_cate_ids)){
                return true;
            }else{//没有权限
                return false;
            }

        }else{//没有找到该栏目
            return false;
        }

    }

    public function loginUser($account,$password){
        $sql = "select * from users where account = :account and password = :password";
        return $this->db()->getRow($sql,array('account'=>$account,'password'=>$password));
    }

    public function addUser($account,$real_name,$nick_name,$password,$email,$tel = ''){
        $sql = "insert into users(account,real_name,nick_name,password,email,tel,last_time,last_ip,add_time) values(:account,:real_name,:nick_name,:password,:email,:tel,now(),:last_ip,now())";
        $password = substr(md5($password.PASSWORD_PREFIX),0,16);
        $bindarr = array(
            'account'=>$account,
            'real_name'=>$real_name,
            'nick_name'=>$nick_name,
            'password'=>$password,
            'email'=>$email,
            'tel'=>$tel,
            'last_ip'=>return_ip(),
        );
        $this->db()->execute($sql,$bindarr);
        return $this->db()->lastInsertId();
    }

    public function updateUser($user_id,$account,$real_name,$nick_name,$password,$email = '',$tel = ''){
        $bindarr = array(
            'account'=>$account,
            'real_name'=>$real_name,
            'nick_name'=>$nick_name,
            'email'=>$email,
            'tel'=>$tel,
            'last_ip'=>return_ip(),
            'last_time'=>date('y-m-d H:i:s'),
            'user_id'=>$user_id,
        );
        if($password){
            $sql = "update users set account = :account,real_name = :real_name,nick_name = :nick_name, password =:password ,email = :email, tel = :tel,last_ip = :last_ip,last_time = :last_time where user_id = :user_id";
            $password = substr(md5($password.PASSWORD_PREFIX),0,16);
            $bindarr['password'] = $password;
        }else{
            $sql = "update users set account = :account,real_name = :real_name,nick_name = :nick_name, email = :email, tel = :tel,last_ip = :last_ip,last_time = :last_time where user_id = :user_id";
        }
        $rs = $this->db()->execute($sql,$bindarr);
        return $rs->rowCount();
    }

    public function delUser($user_id){

        $sql = "delete from users where user_id = :user_id";

        $bindarr = array(
            'user_id'=>$user_id,
        );
        $rs = $this->db()->execute($sql,$bindarr);
        return $rs->rowCount();
    }

    public function getUserList($curr_page = 1 ,$per_page = 50,$filter = array()){

        $where = ' 1=1 ';
        $bindarr = array();

        if(isset($filter['account'])){
            $where .= ' and u.account like :account';
            $bindarr['account'] = '%'.$filter['account'].'%';
        }

        $ret = $this->pager($curr_page, $per_page)
            ->ct('select count(*) from users u where '. $where ,$bindarr)
            ->rs("select u.*,group_concat(r.role_name) as role_names from users u left join user_has_roles ur on u.user_id = ur.user_id left join roles r on r.role_id = ur.role_id where ".$where." group by u.user_id order by u.user_id desc",$bindarr);
        return $ret;
    }

    public function getUsers($is_stop = null){
        $where = ' 1=1 ';
        $bindarr = array();

        if($is_stop !== null){
            $where .= ' and is_stop = :is_stop';
            $bindarr['is_stop'] = $is_stop;
        }

        $sql = "select * from users where " . $where. " order by account";
        $ret = $this->db()->getAll($sql,$bindarr);
        $users = array();
        foreach($ret as $v){
            $users[$v['user_id']] = $v;
        }
        return $users;
    }

    public function getRoleUsers($role_id){

        $sql = "select * from users where user_id in(select distinct u.user_id from users u left join user_has_roles ur on u.user_id = ur.user_id where ur.role_id = :role_id or ur.role_id = 1) order by account";
        $ret = $this->db()->getAll($sql,array('role_id'=>$role_id));
        return $ret;
    }

    public function loginlog($user_id){

        $sql = "update users set last_ip = :last_ip,last_time = :last_time where user_id = :user_id";
        $bindarr = array(
            'last_ip'=>return_ip(),
            'last_time'=>date('Y-m-d H:i:s'),
            'user_id'=>$user_id,
        );
        $rs = $this->db()->execute($sql,$bindarr);

        return $rs->rowCount();
    }

    public function getUserCates($user_id){

        $sql = "select * from cate_has_users where user_id = :user_id";

        $bindarr = array(
            'user_id'=>$user_id,
        );

        return $this->db()->getAll($sql,$bindarr);
    }

    public function getUserRoles($user_id){

        $sql = "select r.* from user_has_roles u left join roles r on u.role_id = r.role_id where user_id = :user_id";

        $bindarr = array(
            'user_id'=>$user_id,
        );

        return $this->db()->getAll($sql,$bindarr);
    }

    public function isSuperAdmin($user_id){
        $sql = "select count(*) from user_has_roles where user_id = :user_id and role_id = 1";
        return $this->db()->getOne($sql,array('user_id'=>$user_id));
    }

    public function updateUserName($user_id,$account){
        $sql = "update users set account = :account where user_id  = :user_id";
        $rs = $this->db()->execute($sql,array('account'=>$account,'user_id'=>$user_id));
        return $rs->rowCount();
    }
}